Hello friends, In this post we will learn about “How to import excel or csv file to MySQL database using PHP”. Before start this topic, i want to discuss the use of this function?. As you know that if we have 10 to 20 records of employee than we can insert this records manually into database, but if we have more than 1000 records than it not a possibility to enter 1000 records manually one by one.
To insert morethan 1000 records at a time, we create a excel or csv file of employee details and import our excel or csv file to mysql database. Now i going to start step by step process to create a panel for import excel to mysql database.
Step 1: First we will create a connection file db.php.
|
2 3 4 5 6 7 |
<?php $conn=mysql_connect("localhost","root","") or die("Could not connect"); mysql_select_db("excel",$conn) or die("could not connect database"); ?> |
Step 2: Create a database table
|
2 3 4 5 6 7 8 9 |
CREATE TABLE IF NOT EXISTS `employee` ( `id` int(100) NOT NULL, `name` varchar(1000) NOT NULL, `email` varchar(1000) NOT NULL, `phone` varchar(1000) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 |
Step 3: Now we will create index.php file for front view.
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
<!DOCTYPE html> <?php include 'db.php'; ?> <html lang="en"> <head> <meta charset="utf-8"> <title>Import Excel To Mysql Database Using PHP </title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <meta name="description" content="Import Excel File To MySql Database Using php"> <link rel="stylesheet" href="css/bootstrap.min.css"> <link rel="stylesheet" href="css/bootstrap-responsive.min.css"> <link rel="stylesheet" href="css/bootstrap-custom.css"> </head> <body> <!-- Navbar ================================================== --> <div class="navbar navbar-inverse navbar-fixed-top"> <div class="navbar-inner"> <div class="container"> <a class="btn btn-navbar" data-toggle="collapse" data-target=".nav-collapse"> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </a> <a class="brand" href="#">Import Excel To Mysql Database Using PHP</a> </div> </div> </div> <div id="wrap"> <div class="container"> <div class="row"> <div class="span3 hidden-phone"></div> <div class="span6" id="form-login"> <form class="form-horizontal well" action="import.php" method="post" name="upload_excel" enctype="multipart/form-data"> <fieldset> <legend>Import CSV/Excel file</legend> <div class="control-group"> <div class="control-label"> <label>CSV/Excel File:</label> </div> <div class="controls"> <input type="file" name="file" id="file" class="input-large"> </div> </div> <div class="control-group"> <div class="controls"> <button type="submit" id="submit" name="Import" class="btn btn-primary button-loading" data-loading-text="Loading...">Upload</button> </div> </div> </fieldset> </form> </div> <div class="span3 hidden-phone"></div> </div> <form action="export.php" method="post" name="export_excel"> <div class="control-group"> <div class="controls"> <button type="submit" id="export" name="export" class="btn btn-primary button-loading" data-loading-text="Loading...">Export MySQL Data to CSV/Excel File</button> </div> </div> </form> <table class="table table-bordered"> <thead> <tr> <th>ID</th> <th>Name</th> <th>Email</th> <th>Phone</th> </tr> </thead> <?php $SQLSELECT = "SELECT * FROM employee "; $result_set = mysql_query($SQLSELECT, $conn); while($row = mysql_fetch_array($result_set)) { ?> <tr> <td><?php echo $row['id']; ?></td> <td><?php echo $row['name']; ?></td> <td><?php echo $row['email']; ?></td> <td><?php echo $row['phone']; ?></td> </tr> <?php } ?> </table> </div> </div> </body> </html> |
Step 4: Now we will create a file import.php to insert excel or csv records.
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
<?php include 'db.php'; if(isset($_POST["Import"])){ echo $filename=$_FILES["file"]["tmp_name"]; if($_FILES["file"]["size"] > 0) { $file = fopen($filename, "r"); while (($data = fgetcsv($file, 10000, ",")) !== FALSE) { //It wiil insert a row to our subject table from our csv file` $sql ="INSERT into employee(name,email,phone) values('$data[0]','$data[1]','$data[2]')"; //we are using mysql_query function. it returns a resource on true else False on error $result = mysql_query( $sql, $conn ); if(! $result ) { echo "<script type=\"text/javascript\"> alert(\"Invalid File:Please Upload CSV File.\"); window.location = \"index.php\" </script>"; } } fclose($file); //throws a message if data successfully imported to mysql database from excel file echo "<script type=\"text/javascript\"> alert(\"CSV File has been successfully Imported.\"); window.location = \"index.php\" </script>"; //close of connection mysql_close($conn); } } ?> |
This is the simple 4 steps to create a panel for import excel or csv file records into MySQL database. I hope it will help you and solve your problems.
Pradeep Maurya is the Professional Web Developer & Designer and the Founder of “Tutorials website”. He lives in Delhi and loves to be a self-dependent person. As an owner, he is trying his best to improve this platform day by day. His passion, dedication and quick decision making ability to stand apart from others. He’s an avid blogger and writes on the publications like Dzone, e27.co
